﻿using System;
using System.Collections.Generic; 
using System.Text;
using System.Linq;
using System.Data;
using Titan.ExpressionAnalyse;
using System.Data.Common;
using System.Collections;
using System.Data.SQLite;

namespace Titan.SQLite
{
    public class SQLiteSqlProvider : SqlProvider
    {
        private const string PARAMETER_PREFIX = "@";
        //private const string PARAMETER_PREFIX_IN_STATEMENT = ":v";
        //private const string PARAMETER_TAG = ":";  


        public override IDbConnection CreateConnection()
        {
            return new SQLiteConnection();
        }

        public override DbDataAdapter CreateDataAdapter()
        {
            return new SQLiteDataAdapter();
        }



        public override void WrapPaging(IDbSession session, IDbCommand command, int pageSize, int pageIndex)
        {
            SQLiteCommand sqliteCommand = (SQLiteCommand)command;
            Util.CheckPageSizeAndPageIndex(pageSize, pageIndex);
             

            StringBuilder sb = new StringBuilder(); 
            sb.Append(command.CommandText);
            sb.Append(" limit ");
            sb.Append(PARAMETER_PREFIX);
            sb.Append("PagerPageSize");
            sb.Append(" offset ");
            sb.Append(PARAMETER_PREFIX);
            sb.Append("PagerOffset");



            int pagerOffset = (pageIndex - 1) * pageSize;
            sqliteCommand.Parameters.Add(PARAMETER_PREFIX + "PagerPageSize", DbType.Int32).Value = pageSize;
            sqliteCommand.Parameters.Add(PARAMETER_PREFIX + "PagerOffset", DbType.Int32).Value = pagerOffset;

            sqliteCommand.CommandText = sb.ToString(); 
        }

        public override object ConvertDbValue(object value, Type expectedType)
        {
            if (value == null || value == DBNull.Value) return null;
            if (expectedType.IsGenericType && expectedType.GetGenericTypeDefinition() == typeof(Nullable<>))
            {
                Type innerType = Nullable.GetUnderlyingType(expectedType);
                return ConvertDbValue(value, innerType);
            }
            if (expectedType.IsEnum)
            {
                Type innerType = Enum.GetUnderlyingType(expectedType);
                return Enum.ToObject(expectedType, ConvertDbValue(value, innerType));
            }
            
            return Convert.ChangeType(value, expectedType);
        }
        protected override IDataParameter CreateParameter(string parameterName, object parameterValue, Type propertyType,int length)
        {
            global::System.Data.SQLite.SQLiteParameter parameter = new global::System.Data.SQLite.SQLiteParameter();
            parameter.ParameterName = parameterName;

            if (parameterValue == null)
            {
                parameter.DbType = GetDbType(propertyType);
                //parameter.Value = Util.GetDefaultValue(propertyType);
                parameter.Value = DBNull.Value;
            }
            else
            {
                if (parameterValue is bool)
                {
                    parameter.Value = (bool)parameterValue ? 1 : 0;
                }
                else if (parameterValue is Enum)
                {
                    parameter.Value = Convert.ChangeType(parameterValue,Enum.GetUnderlyingType(propertyType));
                }
                else
                {
                    parameter.Value = parameterValue;
                }
            }
            return parameter;
        }
        private static readonly Dictionary<Type, DbType> CachedOracleDbTypes = new Dictionary<Type, DbType>() { { typeof(bool), DbType.Byte } };
        /// <summary>
        /// 根据类型获取存储过程参数类型
        /// </summary>
        /// <param name="parameterValueType"></param>
        /// <returns></returns>
        private static DbType GetDbType(Type parameterValueType)
        {
            if (!CachedOracleDbTypes.ContainsKey(parameterValueType))
            {
                lock (CachedOracleDbTypes)
                {
                    if (!CachedOracleDbTypes.ContainsKey(parameterValueType))
                    {
                        Type innerType = parameterValueType;
                        if (innerType.IsGenericType && innerType.GetGenericTypeDefinition() == typeof(Nullable<>))
                        {
                            innerType = Nullable.GetUnderlyingType(innerType);
                        }
                        //nullable中可能还有枚举
                        if (innerType.IsEnum)
                        {
                            innerType = Enum.GetUnderlyingType(innerType);
                        }

                        global::System.Data.SQLite.SQLiteParameter param = new global::System.Data.SQLite.SQLiteParameter { Value = Util.GetDefaultValue(innerType) };
                        CachedOracleDbTypes.Add(parameterValueType, param.DbType);
                    }
                }
            }
            return CachedOracleDbTypes[parameterValueType];
        }
         

        #region 对象操作

        public override int Insert(IDbSession session, IDictionary<string, string> tableMapping, EntityType entityType, INameValueExpressionCollection nameValues, object entity)
        { 
            ITable table = GetTableByEntityType(entityType);


            StringBuilder sb = new StringBuilder();
            SQLiteCommand command = new SQLiteCommand();
            Counter parameterCounter = new Counter(1);

            #region 循环每个属性

            string tableName = GetTableName(entityType, tableMapping);
            sb.Append("insert into ");
            sb.Append(tableName);
            sb.Append(" (");
            //Oracle存储过程参数有顺序之分
            //List<OracleParameter> returnParameters = new List<OracleParameter>();//取回的存储过程
            Dictionary<string, IColumn> returnColumns = new Dictionary<string, IColumn>(StringComparer.OrdinalIgnoreCase);

             
            int index = 0;
            //var propertyNames = insertPropertyNames as string[] ?? insertPropertyNames.ToArray();
            foreach (NameValueExpression nameValue in nameValues)
            {
                if (index > 0) { sb.Append(","); }

                var column = table.Columns[nameValue.PropertyName];
                if (column.InsertBehavior.Generate)
                {
                    sb.Append(column.ColumnName);
                    index++;
                }
            }
            sb.Append(") values (");


            index = 0;
            foreach (NameValueExpression nameValue in nameValues)
            {
                if (index > 0) { sb.Append(","); }
                IColumn column = table.Columns[nameValue.PropertyName];
                if (column.InsertBehavior.Generate)
                {
                    if (column.InsertBehavior.ValueBehavior == ValueBehavior.UseValueExpression)
                    {
                        sb.Append(column.InsertBehavior.ValueExpression);
                    }
                    else
                    {
                        sb.Append(PARAMETER_PREFIX);
                        sb.Append(parameterCounter.CurrentValue);


                        //object propertyValue = column.PropertyAdapter.GetValue(obj);
                        //Validate(column, propertyValue);//验证如：检查字符串长度，是否为null
                        AddParameter(command.Parameters, parameterCounter, PARAMETER_PREFIX, nameValue.PropertyValue, column.PropertyAdapter.PropertyType.Type, column.Size);

                    }
                    index++;
                }
            }
            sb.Append(")");

            //"INSERT INTO SequenceTest_Table (ID, OtherColumn)" +
            //"VALUES (SequenceTest_Sequence.NEXTVAL, :OtherColumn)" +
            //"RETURNING ID INTO :ID";

            foreach (IdentityColumn column in table.PrimaryKeyColumns)
            {
                //取回主键值 ，本版本只支持主键值的取回
                if (column.IsIdentity && column.ReturnAfterInsert)
                {
                    returnColumns.Add(PARAMETER_PREFIX + parameterCounter.CurrentValue, column);

                    sb.Append(";select last_insert_rowid()");
                    break;//只能有一个
                }
            }
            #endregion


            command.CommandText = sb.ToString();
            command.CommandType = CommandType.Text;


            //执行sql
            object returnValue = session.ExecuteScalar(command);

            //read return value  
            foreach (KeyValuePair<string, IColumn> item in returnColumns)
            {
                item.Value.PropertyAdapter.SetValue(entity, ConvertDbValue(returnValue, item.Value.PropertyAdapter.PropertyType.Type));
                break;//只能有一个
            } 

            return 1;
        }

        public override int SelectCollection(IDbSession session, IDictionary<string, string> tableMapping, EntityType entityType, IList list, IQueryExpression queryExpression, bool clearListBeforeFill)
        {
            //检查pageSize和pageIndex是否合法
            if (queryExpression.PageIndex.HasValue && queryExpression.PageSize.HasValue)
            {
                Util.CheckPageSizeAndPageIndex(queryExpression.PageSize.Value, queryExpression.PageIndex.Value);
            }

            SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(this, entityType, queryExpression);


            StringBuilder sb = new StringBuilder();
            IDbCommand cmd = session.Connection.CreateCommand();
            Counter counter = new Counter(1);


            GenerateSelectSql_SelectFromWhereOrder(sb, cmd.Parameters, counter, PARAMETER_PREFIX, PARAMETER_PREFIX, queryExpression, sqlAnalyseResult, tableMapping);


            //执行sql
            cmd.CommandText = sb.ToString();
            cmd.CommandType = CommandType.Text;
            if (queryExpression.PageIndex != null && queryExpression.PageSize != null)
            {
                //分页的时候需要包装分页语句
                WrapPaging(session,cmd, queryExpression.PageSize.Value, queryExpression.PageIndex.Value);
            }

            IDataReader dataReader = session.ExecuteReader(cmd);

            if (clearListBeforeFill)
            {
                list.Clear();
            }

            int readedcount = 0;
            while (dataReader.Read())
            {
                if (list.Count <= readedcount)
                { 
                    list.Add(Activator.CreateInstance(entityType.Type));
                }
                
                object obj =list[readedcount];
                sqlAnalyseResult.ObjectFiller.Fill(this,obj, dataReader);
                readedcount++;
            }
            dataReader.Close();
            int returnCount = list.Count;//注意此处必须返回集合元素个数
            if (queryExpression.ReturnMatchedCount)
            {
                if (sqlAnalyseResult.HasGroup)
                {
                    returnCount = SelectCountGroup(session, tableMapping, entityType, queryExpression,sqlAnalyseResult);
                }
                else
                {
                    returnCount = SelectCount(session, tableMapping, entityType, queryExpression.Wheres);
                }
            }
            return returnCount;
        }
        public int SelectCountGroup(IDbSession session, IDictionary<string, string> tableMapping, EntityType entityType, IQueryExpression queryExpression, SqlAnalyseResult sqlAnalyseResult)
        {


            StringBuilder sb = new StringBuilder();
            IDbCommand command = session.Connection.CreateCommand();
            Counter parameterCounter = new Counter(1);

            //创建一个QueryExpression 


            sb.Append("select count(0) from (");
            //select
            sb.Append("select ");
            foreach (OutputSqlColumn outputSqlColumn in sqlAnalyseResult.SortedOutputSqlColumns)
            {
                sb.Append(outputSqlColumn.TableAliasAndColumnAlias);
                break;//只需要输出一个
            }


            //from where
            GenerateSelectSql_FromWhere(sb, command.Parameters, parameterCounter, PARAMETER_PREFIX, PARAMETER_PREFIX, queryExpression, sqlAnalyseResult, tableMapping);

            sb.Append(") a");








            //执行sql
            command.CommandText = sb.ToString();
            command.CommandType = CommandType.Text;
            IDataReader dataReader = session.ExecuteReader(command);


            int count = 0;
            if (dataReader.Read())
            {
                count = Convert.ToInt32(dataReader[0]);
            }
            dataReader.Close();
            return count;
        }
        public override int SelectCount(IDbSession session, IDictionary<string, string> tableMapping, EntityType entityType, IConditionCollection conditionCollection)
        {


            StringBuilder sb = new StringBuilder();
            IDbCommand command = session.Connection.CreateCommand();
            Counter parameterCounter = new Counter(1);

            //创建一个QueryExpression
            IQueryExpression queryExpression = new QueryExpression();
            queryExpression.Wheres = conditionCollection;
            SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(this, entityType, queryExpression);



            sb.Append("select count(0) ");
            GenerateSelectSql_FromWhere(sb, command.Parameters, parameterCounter, PARAMETER_PREFIX, PARAMETER_PREFIX, queryExpression, sqlAnalyseResult, tableMapping);







            //执行sql
            command.CommandText = sb.ToString();
            command.CommandType = CommandType.Text;
            IDataReader dataReader = session.ExecuteReader(command);


            int count = 0;
            if (dataReader.Read())
            {
                count = Convert.ToInt32(dataReader[0]);
            }
            dataReader.Close();
            return count;
        }

        public override bool Exists(IDbSession session, IDictionary<string, string> tableMapping, EntityType entityType, IConditionCollection conditionCollection)
        {
            IQueryExpression queryExpression = new QueryExpression();
            queryExpression.Wheres = conditionCollection;
            SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(this, entityType, queryExpression);


            StringBuilder sb = new StringBuilder();
            IDbCommand command = session.Connection.CreateCommand();
            Counter parameterCounter = new Counter(1);



            sb.Append("select 0 ");
            GenerateSelectSql_FromWhere(sb, command.Parameters, parameterCounter, PARAMETER_PREFIX, PARAMETER_PREFIX, queryExpression, sqlAnalyseResult, tableMapping); 
            sb.Append(" limit 1");




            //执行sql
            command.CommandText = sb.ToString();
            command.CommandType = CommandType.Text;
            IDataReader dataReader = session.ExecuteReader(command);


            bool exists = dataReader.Read();
            dataReader.Close();
            return exists;
        }

        public override int Delete(IDbSession session, IDictionary<string, string> tableMapping, EntityType entityType, IConditionCollection conditionCollection)
        {
            //ITable table=getTables().getTableByClass(clazz);

            IQueryExpression queryExpression = new QueryExpression();
            queryExpression.Wheres = conditionCollection;
            SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(this, entityType, queryExpression);

            if (sqlAnalyseResult.ForeignTables.Count > 0)
            {
                //不支持
                throw ExceptionFactory.NotSupported("SQLite不支持多表关联更新");
            }

            //执行关联删除 
            StringBuilder sb = new StringBuilder();
            IDbCommand command = session.Connection.CreateCommand();
            Counter parameterCounter = new Counter(1);


            sb.Append("delete from ");
            sb.Append(GetTableName(entityType, tableMapping)); 
            //说明是无关联表的条件，只是本表的条件
            sb.Append(" where ");
            GenerateSelectSql_Where(sb, command.Parameters, parameterCounter, PARAMETER_PREFIX, PARAMETER_PREFIX, queryExpression.Wheres, sqlAnalyseResult,false);


            //执行sql
            command.CommandText = sb.ToString();
            command.CommandType = CommandType.Text;
            int returnCount = session.ExecuteNonQuery(command);


            return returnCount;
        }

        public override int Update(IDbSession session, IDictionary<string, string> tableMapping, EntityType entityType, IUpdateExpression updateExpression)
        {
             
            ITable table = GetTableByEntityType(entityType);



            
            SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(this, entityType, updateExpression);


            if (sqlAnalyseResult.ForeignTables.Count > 0)
            {
                //不支持
                throw ExceptionFactory.NotSupported("SQLite不支持多表关联更新");
            }

            StringBuilder sb = new StringBuilder();
            IDbCommand command = session.Connection.CreateCommand();
            Counter parameterCounter = new Counter(1);



            sb.Append("update ");
            sb.Append(GetTableName(entityType, tableMapping));
            sb.Append(" set ");
            int index = 0;
            foreach (NameValueExpression nameValue in updateExpression.Updates)
            {
                if (index > 0) { sb.Append(","); }
                index++;
                IColumn column = table.Columns[nameValue.PropertyName];
                sb.Append(column.ColumnName);
                sb.Append("=");
                sb.Append(PARAMETER_PREFIX);
                sb.Append(parameterCounter.CurrentValue);


                //object propertyValue = column.PropertyAdapter.GetValue(obj);
                //Validate(column, propertyValue);//验证如：检查字符串长度，是否为null
                AddParameter(command.Parameters, parameterCounter, PARAMETER_PREFIX, nameValue.PropertyValue, column.PropertyAdapter.PropertyType.Type, column.Size);

            }
            //说明是无关联表的条件，只是本表的条件
            sb.Append(" where ");
            GenerateSelectSql_Where(sb, command.Parameters, parameterCounter, PARAMETER_PREFIX, PARAMETER_PREFIX, updateExpression.Wheres, sqlAnalyseResult, false);


            //执行sql
            command.CommandText = sb.ToString();
            command.CommandType = CommandType.Text;
            int returnCount = session.ExecuteNonQuery(command);


            return returnCount;
        }
        #endregion




        protected override IAttributeReader CreateAttributeReader()
        {
            return new SQLiteAttributeReader();
        }
    }
}
